package edu.calpoly.csc.coursebook.business.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.Map.Entry;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;

import edu.calpoly.csc.coursebook.business.model.Course;
import edu.calpoly.csc.coursebook.util.impl.CourseRelevancyScorer;

public class CourseManagerDaoImpl implements CourseManagerDao
{
   /** Loger for this class and subclasses */
   protected final Log log = LogFactory.getLog(this.getClass());
   
   private DataSource ds;
   
   public Collection<Course> getCoursesList()
   {
      log.info("Getting courses!");
      CourseQuery cq = new CourseQuery(ds);
      return cq.execute();
   }
   
   public Collection<Course> getCoursesList(HashMap<String, Double> interests, List<String> origInterests,
		   HashMap<String, String> intToSynMap)
   {
      log.info("searching for courses...");
      List<Course> resultsList;
      
      if (interests.isEmpty())
      {
         resultsList = new ArrayList<Course>(0);
         resultsList.clear();
      }
      else
      {
         InterestsCourseQuery icq = new InterestsCourseQuery(ds, interests); 
         List<Course >courses = icq.execute();
         /* there will be tons of duplicates in this result because of the join, 
          * so clean it up and add keywords and ge areas to the same object... */
         HashMap<String, Course> result = new HashMap<String, Course>();
         for (Course c: courses)
         {
            if (result.containsKey(c.getCode()))
            {
               Course course = result.get(c.getCode());
               Set<Entry<String, Double>> keywords = (c.getKeywords()).entrySet();
               for (Entry<String, Double> entry : keywords) {
            	   course.addKeyword(entry.getKey(), entry.getValue());
               }
               for(String area : c.getGeAreas())
               {
                 if (!course.getGeAreas().contains(area))
                  course.addGeArea(area);
               }
            }
            else
            {
               result.put(c.getCode(), c);
            }
         }
         
         for (Course c: result.values()) {
        	 Set<Entry<String, Double>> keywords = (c.getKeywords()).entrySet();
             for (Entry<String, Double> entry : keywords) {
            	 // adds the original interest related to this course
            	 System.out.println(entry.getKey());
            	 System.out.println(intToSynMap.get(entry.getKey()));
            	 c.addInterest(intToSynMap.get(entry.getKey()));
             }
         }
         resultsList = new ArrayList<Course>(result.values().size());
         for (Course c: result.values())
         {
          /* set the relevancy here */
          CourseRelevancyScorer scorer = CourseRelevancyScorer.getInstance();
          int score = scorer.calculateScore(interests, c.getKeywords(), origInterests);
          c.setRelevancyScore(score);
          resultsList.add(c);
         }
         Collections.sort(resultsList, new Comparator<Course>() {
                public int compare(Course arg0, Course arg1) {
                  return arg1.getRelevancyScore() - arg0.getRelevancyScore();   
                }
         });
      }
       
      return resultsList;
   }

   public void increaseUnits(Course course, int units)
   {
      log.info("Increasing units of " + course.getCode() + " by " + units + "!");
    
      SqlUpdate su = new SqlUpdate(ds, "update courses set units = units + ? where code = ?");
      su.declareParameter(new SqlParameter("increase", Types.INTEGER));
      su.declareParameter(new SqlParameter("code", Types.VARCHAR));
      su.compile();
      
      Object[] oa = new Object[2];
      oa[0] = new Integer(units);
      oa[1] = course.getCode();
      
      int count = su.update(oa);
      log.info("Rows affected: " + count);
   }
   
   public void setDataSource(DataSource ds)
   {
      this.ds = ds;
   }
   
   class CourseQuery extends MappingSqlQuery
   {
      CourseQuery(DataSource ds)
      {
	 super(ds, "SELECT code, title, description, units from courses");
	 compile();
      }
      
      @Override
      protected Object mapRow(ResultSet rs, int rowNum) throws SQLException
      {
	 Course course = new Course();
	 course.setCode(rs.getString("code"));
	 course.setTitle(rs.getString("title"));
	 course.setDescription(rs.getString("description"));
	 course.setUnits(rs.getInt("units"));
	 return course;
      }

   }
   
   /****
    * returns the courses that have keywords that match the ones passed in to 
    * this object's constructor.
    * @author bestrada
    */
   class InterestsCourseQuery extends MappingSqlQuery
   {
      InterestsCourseQuery(DataSource ds, HashMap<String, Double> interests)
      {
         super();
         String query = "SELECT courses.code, courses.title, courses.description, courses.units, " +
         				"category.keyword, ge_area.area, relevance " + 
                        "FROM courses, categories_course, category, courses_ge_area, ge_area " +
                        "WHERE courses.code = categories_course.code AND " +
                              "categories_course.category = category.id AND " +
                              "courses.code = courses_ge_area.code AND " +
                              "ge_area.area = courses_ge_area.area AND " +
                        "UPPER(category.keyword) in (";
         StringBuffer qbuild = new StringBuffer(query);
         
         Set<Entry<String, Double>> keywords = interests.entrySet();
         for (Iterator<Entry<String, Double>> itr = keywords.iterator(); itr.hasNext(); )
         {
            qbuild.append('\'');
            qbuild.append(itr.next().getKey().toUpperCase().replaceAll("'", ""));
            qbuild.append('\'');
            
            /* only put a comma if there's more keywords */
            if (itr.hasNext()) qbuild.append(',');
         }
         
         qbuild.append(')');
         super.setDataSource(ds);
         super.setSql(qbuild.toString());
         super.compile();
      }
      
      @Override
      protected Object mapRow(ResultSet rs, int rowNum) throws SQLException
      {
         Course course = new Course();
         course.setCode(rs.getString("courses.code"));
         course.setTitle(rs.getString("courses.title"));
         course.setDescription(rs.getString("courses.description"));
         course.setUnits(rs.getInt("courses.units"));
         course.addKeyword(rs.getString("category.keyword"), rs.getDouble("relevance"));
         course.addGeArea(rs.getString("ge_area.area"));
         return course;
      }
   }
}
